package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.db.DBConn;
import dao.TopicMagDao;
import domain.Topic;

/**
 * 提问或讨论的处理Dao实现
 * 
 * May 17, 2009
 *
 * @author Fabian
 *
 */
public class TopicMagImpl implements TopicMagDao {

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#addDiscussion(vo.Topic)
	 */
	
	public boolean addDiscussion(Topic t) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "insert into t_topic values (null, ?, ?, ?, ?, now(), null, 0, 0, 0)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setString(1, t.getTitle());
			pstmt.setString(2, t.getContent());
			pstmt.setInt(3, t.getUser_id());
			pstmt.setInt(4, t.getRole_id());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#addQuestion(vo.Topic)
	 */
	
	public boolean addQuestion(Topic t) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "insert into t_topic values (null, ?, ?, ?, ?, now(), null, 0, 0, 1)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setString(1, t.getTitle());
			pstmt.setString(2, t.getContent());
			pstmt.setInt(3, t.getUser_id());
			pstmt.setInt(4, t.getRole_id());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#closeTopic(int)
	 */
	
	public boolean closeTopic(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_topic set status = 1 where id = ?";
		try {
			conn = DBConn.getConn();			
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#getDiscussionTotalCount()
	 */
	
	public int getDiscussionTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_topic where discuss_question = 0";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#getQuestionTotalCount()
	 */
	
	public int getQuestionTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_topic where discuss_question = 1";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		return 0;
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#getPageSuggest(int, int)
	 */
	
	public List<Topic> getPageQuestion(int start, int limit) {
		
		List<Topic> list = new ArrayList<Topic>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String sql = "select * from t_topic where discuss_question = 1 order by create_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Topic t = new Topic();
				t.setId(rs.getInt("id"));				
				t.setTitle(rs.getString("title"));
				t.setContent(rs.getString("content"));
				int user_id = rs.getInt("author_id");
				int roleId = rs.getInt("role_id");				
				t.setPubDate(rs.getTimestamp("create_time"));
				int status = rs.getInt("status");
				t.setReply_count(rs.getInt("reply_count"));
				
				
				if (status == 0) {
					t.setStatus("未解决");
				} else if (status == 1) {
					t.setStatus("已解决");					
				}
								
				if (roleId == 3) {		//如果是学生
					t.setRole("学生");
					String sql3 = "select name from t_student where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql3);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				} else if (roleId == 2) {			//如果是老师
					t.setRole("老师");
					String sql2 = "select name from t_teacher where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql2);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				} else if (roleId == 1) {			//如果是管理员
					t.setRole("管理员");
					String sql1 = "select name from t_admin where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql1);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				}	
				list.add(t);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}		
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#getPageDiscussion(int, int)
	 */
	
	public List<Topic> getPageDiscussion(int start, int limit) {
		List<Topic> list = new ArrayList<Topic>();		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String sql = "select * from t_topic where discuss_question = 0 order by create_time desc limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Topic t = new Topic();
				t.setId(rs.getInt("id"));				
				t.setTitle(rs.getString("title"));
				t.setContent(rs.getString("content"));
				int user_id = rs.getInt("author_id");
				int roleId = rs.getInt("role_id");				
				t.setPubDate(rs.getTimestamp("create_time"));
				int status = rs.getInt("status");
				t.setReply_count(rs.getInt("reply_count"));
				
				if (status == 0) {
					t.setStatus("进行中");
				} else if (status == 1) {
					t.setStatus("已结束");					
				}
								
				if (roleId == 3) {		//如果是学生
					t.setRole("学生");
					String sql3 = "select name from t_student where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql3);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				} else if (roleId == 2) {			//如果是老师
					t.setRole("老师");
					String sql2 = "select name from t_teacher where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql2);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				} else if (roleId == 1) {			//如果是管理员
					t.setRole("管理员");
					String sql1 = "select name from t_admin where id = ?"; 
					pstmt = DBConn.getPstmt(conn, sql1);
					pstmt.setInt(1, user_id);
					rs2 = pstmt.executeQuery();	
					if (rs2.next()) {
						t.setAuthor(rs2.getString(1));
					}
					rs2.close();
				}	
				list.add(t);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}		
	}

	/* (non-Javadoc)
	 * @see dao.TopicMagDao#delTopic(int)
	 */
	
	public boolean delTopic(int id) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql2 = "delete from t_topic where id = ?";
		//删除此主题的所有回复
		String sql1 = "delete from t_topic_reply where topic_id = ?";
		try {
			conn = DBConn.getConn();	
			conn.setAutoCommit(false);			
			pstmt = DBConn.getPstmt(conn, sql1);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();	
			
			pstmt = DBConn.getPstmt(conn, sql2);
			pstmt.setInt(1, id);
			int rsColum2 = pstmt.executeUpdate();	
			if (rsColum2 > 0) {
				conn.commit();
				result = true;
				return result;
			} else {
				conn.rollback();
			}
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return result;
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

}
